package com.F02.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.log4j.Logger;
import org.apache.struts.action.ActionForm;

import com.F02.actionform.F020205ActionForm;
import com.F02.dao.F020205DAO;
import com.F03.actionform.F0306ActionForm;
import com.dao.common.DbOperationExt;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.info.T_S_YHCZXXinfo;
import com.util.CommonTools;
import com.util.ExcelExport;

/**
 * @leip
 * @非标准产品维护
 * @数据库操作类
 * @方法命名规则：静态参数名称、例如：查询方法，方法名 QUERYLIST();
 * @数据库某字段如果的新增值，如果要取当前时间，请在数据库中表时间的字段上设置GETDATE(), @ insert语句执行时，会自动插入时间。避免系统时间于服务器时间不统一造成错误。 @ 例表c_customers的customer_date字段。

 */

public class F020205DAO extends DbOperationExt{
	
	/**
	 * 记录操作日志;
	 */
	Logger log = Logger.getLogger(F020205DAO.class);

	/**
	 * 构造方法获取数据源;
	 * 
	 */
	public F020205DAO() {
		super();
	}

	public F020205DAO(Connection conn) {
		super(conn);
	}

	/**
	 * @查询
	 */
	public List QUERYLIST(F020205ActionForm actionForm,
			HttpServletRequest request) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();
		float totalPrice = 0;
		Map map = null;
		ResultSet rest = null;
		
		msql.append(" SELECT a.manufacturing_order, ");       
		msql.append(" a.product_code,c.[name] product_name, ");
		msql.append(" d.[name] model_name,a.[description], ");
		msql.append(" f.phases,b.material_code,");
		msql.append(" g.[name] material_name,h.[name] measure_name,");
		msql.append(" g.[description] material_description,");
		msql.append(" b.quantity,b.unit_price,");
		msql.append(" b.quantity * convert(float,b.unit_price) total_price");		
		msql.append(" FROM t_not_standard_products a, ");
		msql.append(" t_not_standard_detail b, ");
		msql.append(" t_product_model c,t_product_type d, ");
		msql.append(" t_family e,t_critical_phases f,");
		msql.append(" t_materials g, t_measure_units h");		
		msql.append(" WHERE a.code = b.standard_code ");
		msql.append(" and a.product_type = c.code ");
		msql.append(" and a.model_type = d.code ");
		msql.append(" and a.family_code = e.family_code ");
		msql.append(" and b.serial = f.serial and e.phases_code = f.code ");
		msql.append(" and b.material_code = g.code and g.measure_code=h.id");
		msql.append(" and (a.manufacturing_order='"+CommonTools.null2String(actionForm.getManufacturingOrder())+"' ");
		msql.append(" or a.product_code='"+CommonTools.null2String(actionForm.getProductCode())+"') ");
		msql.append(" order by a.manufacturing_order,b.serial ");
		try {
			log.info("t_not_standard_products QUERYLIST sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());						
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				actionForm.setManufacturingOrder(CommonTools.null2String(rest
						.getString("manufacturing_order")));
				actionForm.setProductCode(CommonTools.null2String(rest
						.getString("product_code")));
				actionForm.setProductTypeName(CommonTools.null2String(rest
						.getString("product_name")));
				actionForm.setModelTypeName(CommonTools.null2String(rest
						.getString("model_name")));
				actionForm.setDescription(CommonTools.null2String(rest
						.getString("description")));
				map.put("phasesName", CommonTools.null2String(rest
						.getString("phases")));
				map.put("materialCode", CommonTools.null2String(rest
						.getString("material_code")));										
				map.put("materialName", CommonTools.null2String(rest
						.getString("material_name")));
				map.put("materialDescription", CommonTools.null2String(rest
						.getString("material_description")));
				map.put("measureName", CommonTools.null2String(rest
						.getString("measure_name")));
				map.put("quantity", CommonTools.null2String(rest
						.getString("quantity")));
				map.put("unitPrice", CommonTools.null2String(rest
						.getString("unit_price")));
				map.put("groupPrice", CommonTools.null2String(rest
						.getString("total_price")));				
				totalPrice = totalPrice + Float.parseFloat(rest
						.getString("total_price"));
				actionForm.setTotalPrice(String.valueOf(totalPrice));
				list.add(map);
			}									
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}
	/**导出非标准产品主体信息*/
	public List getNotStandHead(F020205ActionForm actionForm,List titleList,HttpServletRequest request){
		List headlist = null;
		List returnList = new ArrayList();
		if(!actionForm.getTotalPrice().equals("")){			
			headlist = new ArrayList();
			headlist.add(request.getParameter("manu"));			
			headlist.add(actionForm.getManufacturingOrder());
			headlist.add("");
			headlist.add(request.getParameter("pcode"));
			headlist.add(actionForm.getProductCode());			
			returnList.add(headlist);
			headlist = new ArrayList();
			headlist.add(request.getParameter("ptype"));
			headlist.add(actionForm.getProductTypeName());
			headlist.add("");
			headlist.add(request.getParameter("mtype"));
			headlist.add(actionForm.getModelTypeName());			
			returnList.add(headlist);
			headlist = new ArrayList();
			headlist.add(request.getParameter("decp"));
			headlist.add(actionForm.getDescription());			
			returnList.add(headlist);
			returnList.add(titleList);
		}
		return returnList;
	}	
	/**导出非标准产品明细材料*/
	public String getNotStandBody(F020205ActionForm actionForm){
		StringBuffer sql=new StringBuffer();
		sql.append(" SELECT f.phases,b.material_code, ");		
		sql.append(" g.[name] material_name,");
		sql.append(" g.[description] material_description,");
		sql.append(" b.quantity,h.[name] measure_name,b.unit_price,");
		sql.append(" b.quantity * convert(float,b.unit_price) total_price");		
		sql.append(" FROM t_not_standard_products a, ");
		sql.append(" t_not_standard_detail b, ");
		sql.append(" t_product_model c,t_product_type d, ");
		sql.append(" t_family e,t_critical_phases f,");
		sql.append(" t_materials g, t_measure_units h");		
		sql.append(" WHERE a.code = b.standard_code ");
		sql.append(" and a.product_type = c.code ");
		sql.append(" and a.model_type = d.code ");
		sql.append(" and a.family_code = e.family_code ");
		sql.append(" and b.serial = f.serial and e.phases_code = f.code ");
		sql.append(" and b.material_code = g.code and g.measure_code=h.id");
		sql.append(" and (a.manufacturing_order='"+CommonTools.null2String(actionForm.getManufacturingOrder())+"' ");
		sql.append(" or a.product_code='"+CommonTools.null2String(actionForm.getProductCode())+"') ");
		sql.append(" order by b.serial ");
		return sql.toString();
	}
	/**导出非标准产品总价信息*/
	public List getNotStandFeet(F020205ActionForm actionForm,HttpServletRequest request){
		List collist = new ArrayList();	
		if(!actionForm.getTotalPrice().equals("")){			
			collist.add(request.getParameter("total"));
			collist.add("");
			collist.add("");
			collist.add("");			
			collist.add("");
			collist.add("");
			collist.add("");
			collist.add(actionForm.getTotalPrice());
		}
		return collist;
	}
	/**
	 * 产品BOM明细文件输出
	 * 
	 * @param String fileName   文件信息名称(无扩展名)
	 * @param List titleList   标题
	 * @param String sql ActionForm  sql文



	 * @param HttpServletResponse response
	 */
	 public void exportProductBOM(String fileName, String order,
			 List headList,String sql,List totalList, ActionForm actionForm, HttpServletResponse response)
		throws Exception {
	List list = new ArrayList();
	List colList = new ArrayList();	
	File file = null;
	try {
		file = new File(fileName + ".xls");
		list = queryDataTotalOther(sql, actionForm,totalList);
		WritableWorkbook wwb = Workbook.createWorkbook(file);
		WritableSheet ws = wwb.createSheet("order="+order, 0);		
		int iRow=0;			
			colList = (List)headList.get(0);
			ws.mergeCells(1, 0, 2, 0);
			ws.mergeCells(4, 0, 7, 0);						
			ws.mergeCells(1, 1, 2, 1);
			ws.mergeCells(4, 1, 7, 1);
			ws.mergeCells(1, 2, 7, 2);
			for (iRow = 0; iRow < headList.size(); iRow++) {
				ws.setColumnView(iRow, 15);
				colList = (List)headList.get(iRow);
				
				setExcelData(ws, iRow, colList);
			}									
			for (int iRows = 0; iRows < list.size(); iRows++) {				
				ws.setColumnView(iRows+iRow, 15);
				colList = (List)list.get(iRows);				
				setExcelData(ws, iRows+iRow, colList);
			}						
			ws.mergeCells(0, iRow+list.size()-1, 6,iRow+list.size()-1);
			
			wwb.write();
	
			wwb.close();
			
			fileUpload(fileName + ".xls", file, response);
		
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		
	}
}
	 
		/**
		 * Excel文件内容设定
		 * 
		 * @param WritableSheet
		 *            ws
		 * @param int
		 *            iRow
		 * @param Map
		 *            zbInfo
		 */
		private static void setExcelData(WritableSheet ws, int iRow, List colList) {
			try {				

				for (int i = 0; i < colList.size(); i++) {
					Label lblSjlx = new Label(i, iRow, colList.get(i).toString(), ExcelExport.getSongTi(10,"all")); // 数据
					ws.addCell(lblSjlx);
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	/**
	 * 下载文件
	 * 
	 * @param String fileName   文件信息名称(无扩展名)
	 * @param File file   文件
	 * @param HttpServletResponse response
	 * 
	 */
	public static void fileUpload(String fileName, File file,
			HttpServletResponse response) throws Exception {
		String filelength = String.valueOf(file.length());
		try {
			response.setContentType("application/x-download");
			response.setHeader("Location", fileName);
			response.setHeader("Content-Disposition", "attachment;filename=\""
					+ CommonTools.gBK2ISO_8859_1(fileName));

			response.setContentLength(Integer.parseInt(filelength));
			OutputStream outputStream = response.getOutputStream();
			InputStream inputStream = new FileInputStream(file);
			byte[] buffer = new byte[1024];
			int i = -1;
			while ((i = inputStream.read(buffer)) != -1) {
				outputStream.write(buffer, 0, i);
			}
			outputStream.flush();
			outputStream.close();
			inputStream.close();
			outputStream = null;

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public List queryDataTotalOther(String sql,ActionForm actionForm,List totalList) throws SelectException {
		List list = new ArrayList();
		List collist = null;
		ResultSet rest = null;
		ResultSetMetaData resd = null;
		try {
			rest = super.executeQuery(sql);
			resd = rest.getMetaData();
			while (rest.next()) {
				collist = new ArrayList();
				for (int i = 1; i <= resd.getColumnCount(); i++) {
					collist.add(CommonTools.null2String(rest.getString(i)));
				}
				list.add(collist);
			}
			if(!totalList.isEmpty()){
				list.add(totalList);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest);
		}
		return list;
	}	
}
